package DAO.DAOImple;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import util.DBTool;
import model.Link;
import model.Page;
import model.Resource;
import model.ResourceFile;
import model.ResourceMessage;
import model.User;
import DAO.ResourceDAO;

public class ResourceImple implements ResourceDAO {
	
	/*添加链接
	 * @param link
	 * @return int result / 0 失败 1 成功
	 */
	public int addLink(Link link) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "insert into T_Links(linker,urlPath, AddTime, urlType, description)" +
						"values(?, ?, ?, ?, ?)";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, link.getLinker().getId());
			pstmt.setString(2, link.getUrlPath());
			pstmt.setString(3, (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date())));
			pstmt.setInt(4, link.getUrlType());
			pstmt.setString(5, link.getDescription());
			
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	/*获得链接列表
	 * 
	 * @return List<Link> result / 链接列表
	 */
	public List<Link> getLinkList(Page page) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		List<Link> links = new ArrayList<Link>();
		Link link = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select l.*,u.name, u.id from T_Links as l, T_User as u where l.linker = u.id and l.state = 0" + 
					" limit ?, ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, page.getStartRow());
			pstmt.setInt(2, page.getPageSize());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				link = new Link();
				link.setId(rs.getInt("l.id"));
				link.setLinker(new User());
				link.getLinker().setId(rs.getInt("u.id"));
				link.getLinker().setName(rs.getString("u.name"));
				link.setUrlPath(rs.getString("l.urlPath"));
				link.setAddTime(rs.getTimestamp("l.addTime"));
				link.setUrlType(rs.getInt("l.urlType"));
				link.setDescription(rs.getString("l.description"));
				links.add(link);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return links;
	}

	public int modifyLink(Link link) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "update T_Links set urlPath = ?, urlType = ?, description = ? where id = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setString(1, link.getUrlPath());
			pstmt.setInt(2, link.getUrlType());
			pstmt.setString(3, link.getDescription());
			pstmt.setInt(4, link.getId());
			
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int deleteLink(int id) {
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "delete from T_Links where id = ?";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public boolean uploadFile(ResourceFile resourceFile) {
		boolean result = false;  //默认为false上传不成功
		if(resourceFile.getUploadFileName() != null) {
			String fileName = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date())
							+ (int)(Math.random()*100000); 
			String fileType = resourceFile.getUploadFileName().substring((resourceFile.getUploadFileName().lastIndexOf(".")));
			resourceFile.setUploadFileName(fileName + fileType);
			try {
				FileInputStream fis = new FileInputStream(resourceFile.getUpload());
				resourceFile.setSize(fis.available());   // 所上传文件的真实大小
	            if(resourceFile.getSize() <= resourceFile.getAbleSize()){   //文件小于1M
	            	FileOutputStream fos = new FileOutputStream(resourceFile.getSavePath() + "\\" + resourceFile.getUploadFileName());
	            	byte[] buffer = new byte[1024];
					int len = 0;
					
					while ((len = fis.read(buffer)) > 0) {
						fos.write(buffer, 0, len);
					}
					
					result = true;
	            }else {
	            	System.out.println("文件过大");
	            }
	            
			} catch (IOException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}else {
			System.out.println("文件为空");
		}
		return result;
	}

	public int addFileInfo(Resource resource) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "insert into T_Resources(uploader, docName, docType, docPath, docSize, docGroup, uploadDate, description, downloadType)" +
				"values(?, ?, ?, ?, ?, ?, ?, ?, ?)";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1,resource.getUploader().getId());
			pstmt.setString(2, resource.getDocName());
			pstmt.setString(3, resource.getDocType());
			pstmt.setString(4, resource.getDocPath());
			pstmt.setInt(5, resource.getDocSize());
			pstmt.setInt(6, resource.getDocGroup());
			pstmt.setString(7, new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()));
			pstmt.setString(8, resource.getDescription());
			pstmt.setString(9, resource.getDownloadType());
			
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public List<Resource> getResourcelist(Page page) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		List<Resource> resources = new ArrayList<Resource>();
		Resource resource = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select r.*,u.name, u.id from T_Resources as r, T_User as u where r.uploader = u.id and r.state = 0" +
					" limit ?, ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, page.getStartRow());
			pstmt.setInt(2, page.getPageSize());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				resource = new Resource();
				resource.setId(rs.getInt("r.id"));
				resource.setUploader(new User());
				resource.getUploader().setId(rs.getInt("u.id"));
				resource.getUploader().setName(rs.getString("u.name"));
				resource.setDocName(rs.getString("r.docName"));
				resource.setDocType(rs.getString("r.docType"));
				resource.setDocPath(rs.getString("docPath"));
				resource.setDocSize(rs.getInt("r.docSize"));
				resource.setDocGroup(rs.getInt("r.docGroup"));
				resource.setUploadDate(rs.getTimestamp("r.uploadDate"));
				resource.setDownload(rs.getInt("r.download"));
				resource.setDescription(rs.getString("r.description"));
				resource.setState(rs.getInt("r.state"));
				
				resources.add(resource);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return resources;
	}

	public Resource getResourceById(int id) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		Resource resource = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select r.*,u.name, u.id from T_Resources as r, T_User as u where r.uploader = u.id and r.state = 0" + 
						" and r.id = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				resource = new Resource();
				resource.setId(rs.getInt("r.id"));
				resource.setUploader(new User());
				resource.getUploader().setId(rs.getInt("u.id"));
				resource.getUploader().setName(rs.getString("u.name"));
				resource.setDocName(rs.getString("r.docName"));
				resource.setDocType(rs.getString("r.docType"));
				resource.setDocPath(rs.getString("docPath"));
				resource.setDocSize(rs.getInt("r.docSize"));
				resource.setDocGroup(rs.getInt("r.docGroup"));
				resource.setUploadDate(rs.getTimestamp("r.uploadDate"));
				resource.setDownload(rs.getInt("r.download"));
				resource.setDescription(rs.getString("r.description"));
				resource.setState(rs.getInt("r.state"));
				
			}else {
				System.out.println("获取资源信息失败");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return resource;	
	}

	public int addRMessage(ResourceMessage resourceMessage) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "insert into T_ResourcesMessage(resourceId,userId, messageTime, content)" +
						"values(?, ?, ?, ?)";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, resourceMessage.getResource().getId());
			pstmt.setInt(2, resourceMessage.getUser().getId());
			pstmt.setString(3, (new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date())));
			pstmt.setString(4, resourceMessage.getContent());
			
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public List<ResourceMessage> getResourceMessage(int id, Page page) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		List<ResourceMessage> resourceMessages = new ArrayList<ResourceMessage>();
		ResourceMessage resourceMessage = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select rm.*,u.name, u.id from T_ResourcesMessage as rm, T_User as u "
					+ "where rm.userId = u.id and rm.state = 0 and rm.resourceId = ?" + 
					" limit ?, ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			pstmt.setInt(2, page.getStartRow());
			pstmt.setInt(3, page.getPageSize());
			
			rs = pstmt.executeQuery();
			while(rs.next()) {
				resourceMessage = new ResourceMessage();
				resourceMessage.setId(rs.getInt("rm.id"));
				resourceMessage.setResource(new Resource());
				resourceMessage.getResource().setId(rs.getInt("rm.resourceId"));
				resourceMessage.setUser(new User());
				resourceMessage.getUser().setId(rs.getInt("u.id"));
				resourceMessage.getUser().setName(rs.getString("u.name"));
				resourceMessage.setMessageTime(rs.getTimestamp("rm.messageTime"));
				resourceMessage.setContent(rs.getString("rm.content"));
				resourceMessage.setState(rs.getInt("rm.state"));
				
				resourceMessages.add(resourceMessage);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return resourceMessages;
	}

	public int deleteRMessage(int id) {
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "delete from T_ResourcesMessage where id = ?";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	/*修改资源信息
	 * @param Resource 
	 * @return int result / 0 失败 1 成功 
	 */
	public int modifyResource(Resource resource) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "update T_Resources set docName = ?, docGroup = ?, description = ? where id = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setString(1, resource.getDocName());
			pstmt.setInt(2, resource.getDocGroup());
			pstmt.setString(3, resource.getDescription());
			pstmt.setInt(4, resource.getId());
			
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int deleteResource(int id) {
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "delete from T_Resources where id = ?";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public List<Resource> getResourcelist(int id) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		List<Resource> resources = new ArrayList<Resource>();
		Resource resource = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select r.*,u.name, u.id from T_Resources as r, T_User as u where r.uploader = ? and  r.uploader = u.id and r.state = 0";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
//			pstmt.setInt(1, page.getStartRow());
//			pstmt.setInt(2, page.getPageSize());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				resource = new Resource();
				resource.setId(rs.getInt("r.id"));
				resource.setUploader(new User());
				resource.getUploader().setId(rs.getInt("u.id"));
				resource.getUploader().setName(rs.getString("u.name"));
				resource.setDocName(rs.getString("r.docName"));
				resource.setDocType(rs.getString("r.docType"));
				resource.setDocPath(rs.getString("docPath"));
				resource.setDocSize(rs.getInt("r.docSize"));
				resource.setDocGroup(rs.getInt("r.docGroup"));
				resource.setUploadDate(rs.getTimestamp("r.uploadDate"));
				resource.setDownload(rs.getInt("r.download"));
				resource.setDescription(rs.getString("r.description"));
				resource.setState(rs.getInt("r.state"));
				
				resources.add(resource);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return resources;
	}

	public List<Link> getLinkList(int id) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;

		List<Link> links = new ArrayList<Link>();
		Link link = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "select l.*,u.name, u.id from T_Links as l, T_User as u where l.linker = ? and l.linker = u.id and l.state = 0";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
//			pstmt.setInt(1, page.getStartRow());
//			pstmt.setInt(2, page.getPageSize());
			rs = pstmt.executeQuery();
			while(rs.next()) {
				link = new Link();
				link.setId(rs.getInt("l.id"));
				link.setLinker(new User());
				link.getLinker().setId(rs.getInt("u.id"));
				link.getLinker().setName(rs.getString("u.name"));
				link.setUrlPath(rs.getString("l.urlPath"));
				link.setAddTime(rs.getTimestamp("l.addTime"));
				link.setUrlType(rs.getInt("l.urlType"));
				link.setDescription(rs.getString("l.description"));
				links.add(link);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return links;
	}

	public int addDownloadTimes(int id) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "update T_Resources set download = download+1 where id = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int deleteResourceFile(String docPath) {
		int result = 0; //默认为0
		String filePath = docPath;
		
		File file = new File(filePath);
		if (file.exists()) {
			boolean d = file.delete();
			if (d) {
				result = 1;
				//System.out.print("删除成功！");
			}
		}else {
			result = 2;
			System.out.println("文件不存在！");
		}
		return 0;
	}
}
